﻿using Dapper;
using System.Data.SqlClient;
using yunbo.Model.BI;
using static System.Runtime.InteropServices.JavaScript.JSType;

namespace yunbo.Repositorys.BIRepositorys
{
	public class GCRepository
	{
		public string data = "Data Source=121.37.93.71;Initial Catalog=FanXingMES;User Id=sa;Password=geili@2025;TrustServerCertificate=true;";

		#region 工厂部分

		/// <summary>
		/// 获取工厂
		/// </summary>
		/// <param name="str"></param>
		/// <param name="date1"></param>
		/// <param name="date2"></param>
		/// <returns></returns>
		public List<Model.BI.GC> GetGC(string? str, DateTime? date1, DateTime? date2)
		{
			List<Model.BI.GC> gc = new List<Model.BI.GC>();
			using (SqlConnection connection = new SqlConnection(data))
			{
				// 基础 SQL
				string sql = @"SELECT gc_id, gc_create_time, gc_code, gc_name, gc_address, gc_remark, gc_create_by
                      FROM bd_gc WHERE [gc_isdelete] = 1 ";

				// 动态构建 WHERE 子句
				List<string> conditions = new List<string>();
				if (!string.IsNullOrEmpty(str))
				{
					conditions.Add("([gc_code] LIKE @str OR [gc_name] LIKE @str OR [gc_address] LIKE @str)");
				}
				if (date1 != null && date2 != null)
				{
					conditions.Add("gc_create_time BETWEEN @date1 AND @date2");
				}
				else if (date1 != null)
				{
					conditions.Add("gc_create_time >= @date1");
				}
				else if (date2 != null)
				{
					conditions.Add("gc_create_time <= @date2");
				}

				// 拼接条件
				if (conditions.Count > 0)
				{
					sql += " AND " + string.Join(" AND ", conditions);
				}

				connection.Open();
				using (SqlCommand command = new SqlCommand(sql, connection))
				{
					// 参数化查询
					if (!string.IsNullOrEmpty(str))
					{
						command.Parameters.AddWithValue("@str", $"%{str}%");
					}
					if (date1 != null)
					{
						command.Parameters.AddWithValue("@date1", date1.Value.Date);
					}
					if (date2 != null)
					{
						command.Parameters.AddWithValue("@date2", date2.Value.Date.AddDays(1).AddSeconds(-1)); // 包含当天
					}

					using (SqlDataReader reader = command.ExecuteReader())
					{
						while (reader.Read())
						{
							gc.Add(new Model.BI.GC
							{
								gc_id = Convert.ToInt32(reader["gc_id"]),
								gc_create_time = Convert.ToDateTime(reader["gc_create_time"]),
								gc_code = reader["gc_code"].ToString(),
								gc_name = reader["gc_name"].ToString(),
								gc_address = reader["gc_address"].ToString(),
								gc_remark = reader["gc_remark"].ToString(),
								gc_create_by = reader["gc_create_by"] is DBNull ? 0 : Convert.ToInt32(reader["gc_create_by"]),
							});
						}
					}
				}
			}
			return gc;
		}

		/// <summary>
		/// 新增工厂
		/// </summary>
		/// <param name="gc"></param>
		/// <returns></returns>
		public bool AddGC(Model.BI.GC gc)
		{
			string sql = @"insert into [dbo].[bd_gc]
(gc_code, gc_name, gc_address, gc_remark, gc_create_by)
values
(@code,@name,@address,@remark,1)";

			try
			{
				using (SqlConnection connection = new SqlConnection(data))
				{
					connection.Open();
					using (SqlCommand command = new SqlCommand(sql, connection))
					{
						command.Parameters.AddWithValue("@code", gc.gc_code);
						command.Parameters.AddWithValue("@name", gc.gc_name);
						command.Parameters.AddWithValue("@address", gc.gc_address);
						command.Parameters.AddWithValue("@remark", gc.gc_remark);

						int rowsAffected = command.ExecuteNonQuery();
						return rowsAffected > 0; // 如果插入成功，返回 true
					}
				}
			}
			catch (Exception ex)
			{
				// 记录日志或处理异常
				Console.WriteLine("An error occurred: " + ex.Message);
				return false; // 如果发生异常，返回 false
			}
		}

		/// <summary>
		/// 修改工厂
		/// </summary>
		/// <param name="gc"></param>
		/// <returns></returns>
		public bool UpdateGC(Model.BI.GC gc)
		{
			string sql = @"update [dbo].[bd_gc] set 
gc_name=@name, gc_address=@address, gc_remark=@remark,
[gc_update_by]=1,[gc_update_time]=getdate()
where [gc_id]=@id";

			try
			{
				using (SqlConnection connection = new SqlConnection(data))
				{
					connection.Open();
					using (SqlCommand command = new SqlCommand(sql, connection))
					{
						command.Parameters.AddWithValue("@name", gc.gc_name);
						command.Parameters.AddWithValue("@address", gc.gc_address);
						command.Parameters.AddWithValue("@remark", gc.gc_remark);
						command.Parameters.AddWithValue("@id", gc.gc_id);

						int rowsAffected = command.ExecuteNonQuery();
						return rowsAffected > 0; // 如果插入成功，返回 true
					}
				}
			}
			catch (Exception ex)
			{
				// 记录日志或处理异常
				Console.WriteLine("An error occurred: " + ex.Message);
				return false; // 如果发生异常，返回 false
			}
		}

		/// <summary>
		/// 删除工厂
		/// </summary>
		/// <param name="id"></param>
		/// <returns></returns>
		public bool DelGC(int id)
		{
			try
			{
				using (SqlConnection connection = new SqlConnection(data))
				{
					string sql = @"UPDATE [dbo].[bd_gc] 
                       SET gc_isdelete = 0 
                       WHERE gc_id = @Id";

					return connection.Execute(sql, new { Id = id }) > 0;
				}
			}
			catch (Exception ex)
			{
				// 记录日志等异常处理
				Console.WriteLine($"删除失败: {ex.Message}");
				return false;
			}
		}

		/// <summary>
		/// 批量删除工厂
		/// </summary>
		/// <param name="ids"></param>
		/// <returns></returns>
		public bool DelGCs(int[] ids)
		{
			// 参数验证
			if (ids == null || ids.Length == 0)
				return false;

			try
			{
				using (SqlConnection connection = new SqlConnection(data))
				{
					connection.Open();
					using (var transaction = connection.BeginTransaction()) // 添加事务
					{
						try
						{
							string sql = @"UPDATE [dbo].[bd_gc] 
		                                 SET gc_isdelete = 0 
		                                 WHERE gc_id IN @Ids";

							int affectedRows = connection.Execute(sql, new { Ids = ids }, transaction);
							transaction.Commit(); // 提交事务

							// 确保所有ID都成功更新
							return affectedRows == ids.Length;
						}
						catch
						{
							transaction.Rollback(); // 回滚事务
							throw;
						}
					}
				}
			}
			catch (Exception ex)
			{
				// 记录日志等异常处理
				Console.WriteLine($"批量删除失败: {ex.Message}");
				return false;
			}
		}

		#endregion

		#region 车间部分

		/// <summary>
		/// 获取车间
		/// </summary>
		/// <param name="gc_id"></param>
		/// <param name="str"></param>
		/// <param name="date1"></param>
		/// <param name="date2"></param>
		/// <returns></returns>
		public List<CJ> GetCJ(int gc_id, string? str, DateTime? date1, DateTime? date2)
		{
			List<CJ> cj = new List<CJ>();
			using (SqlConnection connection = new SqlConnection(data))
			{
				// 基础 SQL
				string sql = @"SELECT cj_id, cj_create_time, cj_code, cj_name,
                      (SELECT [gc_name] FROM [dbo].[bd_gc] a WHERE a.[gc_id] = b.[gc_id]) gc_name,
                      gc_id, cj_remark, cj_create_by
                      FROM bd_cj b WHERE [cj_isdelete] = 1 AND b.gc_id = @gc_id ";

				// 动态构建 WHERE 子句
				List<string> conditions = new List<string>();
				if (!string.IsNullOrEmpty(str))
				{
					conditions.Add("([cj_code] LIKE @str OR [cj_name] LIKE @str)");
				}
				if (date1 != null && date2 != null)
				{
					conditions.Add("cj_create_time BETWEEN @date1 AND @date2");
				}
				else if (date1 != null)
				{
					conditions.Add("cj_create_time >= @date1");
				}
				else if (date2 != null)
				{
					conditions.Add("cj_create_time <= @date2");
				}

				// 拼接条件
				if (conditions.Count > 0)
				{
					sql += " AND " + string.Join(" AND ", conditions);
				}

				connection.Open();
				using (SqlCommand command = new SqlCommand(sql, connection))
				{
					// 参数化查询
					command.Parameters.AddWithValue("@gc_id", gc_id);
					if (!string.IsNullOrEmpty(str))
					{
						command.Parameters.AddWithValue("@str", $"%{str}%");
					}
					if (date1 != null)
					{
						command.Parameters.AddWithValue("@date1", date1.Value.Date);
					}
					if (date2 != null)
					{
						command.Parameters.AddWithValue("@date2", date2.Value.Date.AddDays(1).AddSeconds(-1)); // 包含当天
					}

					using (SqlDataReader reader = command.ExecuteReader())
					{
						while (reader.Read())
						{
							cj.Add(new CJ
							{
								cj_id = Convert.ToInt32(reader["cj_id"]),
								cj_create_time = Convert.ToDateTime(reader["cj_create_time"]),
								cj_code = reader["cj_code"].ToString(),
								cj_name = reader["cj_name"].ToString(),
								gc_name = reader["gc_name"].ToString(),
								gc_id = Convert.ToInt32(reader["gc_id"]),
								cj_remark = reader["cj_remark"].ToString(),
								cj_create_by = reader["cj_create_by"] is DBNull ? 0 : Convert.ToInt32(reader["cj_create_by"]),
							});
						}
					}
				}
			}
			return cj;
		}

		/// <summary>
		/// 获取车间
		/// </summary>
		/// <param name="gc_id"></param>
		/// <param name="str"></param>
		/// <param name="date1"></param>
		/// <param name="date2"></param>
		/// <returns></returns>
		public List<CJ> GetCJbyCode(string? code)
		{
			List<CJ> cj = new List<CJ>();
			using (SqlConnection connection = new SqlConnection(data))
			{
				// 基础 SQL
				string sql = @"SELECT cj_id, cj_create_time, cj_code, cj_name,
                      (SELECT [gc_name] FROM [dbo].[bd_gc] a WHERE a.[gc_id] = b.[gc_id]) gc_name,
                      gc_id, cj_remark, cj_create_by
                      FROM bd_cj b WHERE [cj_isdelete] = 1 ";

				// 动态构建 WHERE 子句
				List<string> conditions = new List<string>();
				if (code!=null)
				{
					conditions.Add(" ([cj_code] = @code) ");
				}

				// 拼接条件
				if (conditions.Count > 0)
				{
					sql += " AND " + string.Join(" AND ", conditions);
				}

				connection.Open();
				using (SqlCommand command = new SqlCommand(sql, connection))
				{
					if (code != null)
					{
						command.Parameters.AddWithValue("@code", code);
					}

					using (SqlDataReader reader = command.ExecuteReader())
					{
						while (reader.Read())
						{
							cj.Add(new CJ
							{
								cj_id = Convert.ToInt32(reader["cj_id"]),
								cj_create_time = Convert.ToDateTime(reader["cj_create_time"]),
								cj_code = reader["cj_code"].ToString(),
								cj_name = reader["cj_name"].ToString(),
								gc_name = reader["gc_name"].ToString(),
								gc_id = Convert.ToInt32(reader["gc_id"]),
								cj_remark = reader["cj_remark"].ToString(),
								cj_create_by = reader["cj_create_by"] is DBNull ? 0 : Convert.ToInt32(reader["cj_create_by"]),
							});
						}
					}
				}
			}
			return cj;
		}


		/// <summary>
		/// 新增车间
		/// </summary>
		/// <param name="cj"></param>
		/// <returns></returns>
		public bool AddCJ(CJ cj)
		{
			string sql = @"insert into [dbo].[bd_cj]
(gc_id, cj_code, cj_name, cj_remark, cj_create_by)
values
(@gc_id,@code,@name,@remark,1)";

			try
			{
				using (SqlConnection connection = new SqlConnection(data))
				{
					connection.Open();
					using (SqlCommand command = new SqlCommand(sql, connection))
					{
						command.Parameters.AddWithValue("@gc_id", cj.gc_id);
						command.Parameters.AddWithValue("@code", cj.cj_code);
						command.Parameters.AddWithValue("@name", cj.cj_name);
						command.Parameters.AddWithValue("@remark", cj.cj_remark);

						int rowsAffected = command.ExecuteNonQuery();
						return rowsAffected > 0; // 如果插入成功，返回 true
					}
				}
			}
			catch (Exception ex)
			{
				// 记录日志或处理异常
				Console.WriteLine("An error occurred: " + ex.Message);
				return false; // 如果发生异常，返回 false
			}
		}

		/// <summary>
		/// 修改车间
		/// </summary>
		/// <param name="cj"></param>
		/// <returns></returns>
		public bool UpdateCJ(CJ cj)
		{
			string sql = @"update [dbo].[bd_cj] set 
gc_id=@gc_id, cj_name=@name, cj_remark=@remark,
[cj_update_by]=1,[cj_update_time]=getdate()
where [cj_id]=@id";

			try
			{
				using (SqlConnection connection = new SqlConnection(data))
				{
					connection.Open();
					using (SqlCommand command = new SqlCommand(sql, connection))
					{
						command.Parameters.AddWithValue("@gc_id", cj.gc_id);
						command.Parameters.AddWithValue("@name", cj.cj_name);
						command.Parameters.AddWithValue("@remark", cj.cj_remark);
						command.Parameters.AddWithValue("@id", cj.cj_id);

						int rowsAffected = command.ExecuteNonQuery();
						return rowsAffected > 0; // 如果插入成功，返回 true
					}
				}
			}
			catch (Exception ex)
			{
				// 记录日志或处理异常
				Console.WriteLine("An error occurred: " + ex.Message);
				return false; // 如果发生异常，返回 false
			}
		}

		/// <summary>
		/// 删除车间
		/// </summary>
		/// <param name="id"></param>
		/// <returns></returns>
		public bool DelCJ(int id)
		{
			try
			{
				using (SqlConnection connection = new SqlConnection(data))
				{
					string sql = @"UPDATE [dbo].[bd_cj] 
                       SET cj_isdelete = 0 
                       WHERE cj_id = @Id";

					return connection.Execute(sql, new { Id = id }) > 0;
				}
			}
			catch (Exception ex)
			{
				// 记录日志等异常处理
				Console.WriteLine($"删除失败: {ex.Message}");
				return false;
			}
		}

		/// <summary>
		/// 批量删除车间
		/// </summary>
		/// <param name="ids"></param>
		/// <returns></returns>
		public bool DelCJs(int[] ids)
		{
			// 参数验证
			if (ids == null || ids.Length == 0)
				return false;

			try
			{
				using (SqlConnection connection = new SqlConnection(data))
				{
					connection.Open();
					using (var transaction = connection.BeginTransaction()) // 添加事务
					{
						try
						{
							string sql = @"UPDATE [dbo].[bd_cj] 
		                                 SET cj_isdelete = 0
		                                 WHERE cj_id IN @Ids";

							int affectedRows = connection.Execute(sql, new { Ids = ids }, transaction);
							transaction.Commit(); // 提交事务

							// 确保所有ID都成功更新
							return affectedRows == ids.Length;
						}
						catch
						{
							transaction.Rollback(); // 回滚事务
							throw;
						}
					}
				}
			}
			catch (Exception ex)
			{
				// 记录日志等异常处理
				Console.WriteLine($"批量删除失败: {ex.Message}");
				return false;
			}
		}

		#endregion

		#region 生产线部分

		/// <summary>
		/// 获取生产线
		/// </summary>
		/// <param name="cj_id"></param>
		/// <param name="str"></param>
		/// <param name="date1"></param>
		/// <param name="date2"></param>
		/// <returns></returns>
		public List<SCX> GetSCX(int cj_id, string? str, DateTime? date1, DateTime? date2)
		{
			List<SCX> scx = new List<SCX>();
			using (SqlConnection connection = new SqlConnection(data))
			{
				// 基础 SQL
				string sql = @"SELECT scx_id, scx_create_time, scx_code, scx_name,
                      (SELECT (SELECT gc_name FROM bd_gc a WHERE a.gc_id = b.gc_id) 
                       FROM bd_cj b WHERE b.cj_id = c.cj_id) gc_name,
                      (SELECT cj_name FROM bd_cj b WHERE b.cj_id = c.cj_id) cj_name,
                      cj_id, scx_remark, scx_create_by
                      FROM bd_scx c WHERE [scx_isdelete] = 1 AND c.cj_id = @cj_id ";

				// 动态构建 WHERE 子句
				List<string> conditions = new List<string>();
				if (!string.IsNullOrEmpty(str))
				{
					conditions.Add("([scx_code] LIKE @str OR [scx_name] LIKE @str)");
				}
				if (date1 != null && date2 != null)
				{
					conditions.Add("scx_create_time BETWEEN @date1 AND @date2");
				}
				else if (date1 != null)
				{
					conditions.Add("scx_create_time >= @date1");
				}
				else if (date2 != null)
				{
					conditions.Add("scx_create_time <= @date2");
				}

				// 拼接条件
				if (conditions.Count > 0)
				{
					sql += " AND " + string.Join(" AND ", conditions);
				}

				connection.Open();
				using (SqlCommand command = new SqlCommand(sql, connection))
				{
					// 参数化查询
					command.Parameters.AddWithValue("@cj_id", cj_id);
					if (!string.IsNullOrEmpty(str))
					{
						command.Parameters.AddWithValue("@str", $"%{str}%");
					}
					if (date1 != null)
					{
						command.Parameters.AddWithValue("@date1", date1.Value.Date);
					}
					if (date2 != null)
					{
						command.Parameters.AddWithValue("@date2", date2.Value.Date.AddDays(1).AddSeconds(-1)); // 包含当天
					}

					using (SqlDataReader reader = command.ExecuteReader())
					{
						while (reader.Read())
						{
							scx.Add(new SCX
							{
								scx_id = Convert.ToInt32(reader["scx_id"]),
								scx_create_time = Convert.ToDateTime(reader["scx_create_time"]),
								scx_code = reader["scx_code"].ToString(),
								scx_name = reader["scx_name"].ToString(),
								gc_name = reader["gc_name"].ToString(),
								cj_name = reader["cj_name"].ToString(),
								cj_id = Convert.ToInt32(reader["cj_id"]),
								scx_remark = reader["scx_remark"].ToString(),
								scx_create_by = reader["scx_create_by"] is DBNull ? 0 : Convert.ToInt32(reader["scx_create_by"]),
							});
						}
					}
				}
			}
			return scx;
		}

		/// <summary>
		/// 新增生产线
		/// </summary>
		/// <param name="scx"></param>
		/// <returns></returns>
		public bool AddSCX(SCX scx)
		{
			string sql = @"insert into [dbo].[bd_scx]
(cj_id, scx_code, scx_name, scx_remark, scx_create_by)
values
(@cj_id,@code,@name,@remark,1)";

			try
			{
				using (SqlConnection connection = new SqlConnection(data))
				{
					connection.Open();
					using (SqlCommand command = new SqlCommand(sql, connection))
					{
						command.Parameters.AddWithValue("@cj_id", scx.cj_id);
						command.Parameters.AddWithValue("@code", scx.scx_code);
						command.Parameters.AddWithValue("@name", scx.scx_name);
						command.Parameters.AddWithValue("@remark", scx.scx_remark);

						int rowsAffected = command.ExecuteNonQuery();
						return rowsAffected > 0; // 如果插入成功，返回 true
					}
				}
			}
			catch (Exception ex)
			{
				// 记录日志或处理异常
				Console.WriteLine("An error occurred: " + ex.Message);
				return false; // 如果发生异常，返回 false
			}
		}

		/// <summary>
		/// 修改生产线
		/// </summary>
		/// <param name="scx"></param>
		/// <returns></returns>
		public bool UpdateSCX(SCX scx)
		{
			string sql = @"update [dbo].[bd_scx] set 
cj_id=@cj_id,scx_name=@name, scx_remark=@remark,
[scx_update_by]=1,[scx_update_time]=getdate()
where [scx_id]=@id";

			try
			{
				using (SqlConnection connection = new SqlConnection(data))
				{
					connection.Open();
					using (SqlCommand command = new SqlCommand(sql, connection))
					{
						command.Parameters.AddWithValue("@cj_id", scx.cj_id);
						command.Parameters.AddWithValue("@name", scx.scx_name);
						command.Parameters.AddWithValue("@remark", scx.scx_remark);
						command.Parameters.AddWithValue("@id", scx.scx_id);

						int rowsAffected = command.ExecuteNonQuery();
						return rowsAffected > 0; // 如果插入成功，返回 true
					}
				}
			}
			catch (Exception ex)
			{
				// 记录日志或处理异常
				Console.WriteLine("An error occurred: " + ex.Message);
				return false; // 如果发生异常，返回 false
			}
		}

		/// <summary>
		/// 删除生产线
		/// </summary>
		/// <param name="id"></param>
		/// <returns></returns>
		public bool DelSCX(int id)
		{
			try
			{
				using (SqlConnection connection = new SqlConnection(data))
				{
					string sql = @"UPDATE [dbo].[bd_scx] 
                       SET scx_isdelete = 0 
                       WHERE scx_id = @Id";

					return connection.Execute(sql, new { Id = id }) > 0;
				}
			}
			catch (Exception ex)
			{
				// 记录日志等异常处理
				Console.WriteLine($"删除失败: {ex.Message}");
				return false;
			}
		}

		/// <summary>
		/// 批量删除生产线
		/// </summary>
		/// <param name="ids"></param>
		/// <returns></returns>
		public bool DelSCXs(int[] ids)
		{
			// 参数验证
			if (ids == null || ids.Length == 0)
				return false;

			try
			{
				using (SqlConnection connection = new SqlConnection(data))
				{
					connection.Open();
					using (var transaction = connection.BeginTransaction()) // 添加事务
					{
						try
						{
							string sql = @"UPDATE [dbo].[bd_scx] 
		                                 SET scx_isdelete = 0 
		                                 WHERE scx_id IN @Ids";

							int affectedRows = connection.Execute(sql, new { Ids = ids }, transaction);
							transaction.Commit(); // 提交事务

							// 确保所有ID都成功更新
							return affectedRows == ids.Length;
						}
						catch
						{
							transaction.Rollback(); // 回滚事务
							throw;
						}
					}
				}
			}
			catch (Exception ex)
			{
				// 记录日志等异常处理
				Console.WriteLine($"批量删除失败: {ex.Message}");
				return false;
			}
		}

		#endregion

		#region 工位部分

		/// <summary>
		/// 获取工位
		/// </summary>
		/// <param name="scx_id"></param>
		/// <param name="str"></param>
		/// <param name="date1"></param>
		/// <param name="date2"></param>
		/// <returns></returns>
		public List<GW> GetGW(int scx_id, string? str, DateTime? date1, DateTime? date2)
		{
			List<GW> gw = new List<GW>();
			using (SqlConnection connection = new SqlConnection(data))
			{
				// 基础 SQL
				string sql = @"SELECT d.gw_id, d.gw_create_time, d.gw_code, d.gw_name,
                      a.gc_name, b.cj_name, c.scx_name,
                      d.scx_id, d.gw_remark, d.gw_create_by
                      FROM bd_gw d
                      JOIN bd_scx c ON d.scx_id = c.scx_id
                      JOIN bd_cj b ON c.cj_id = b.cj_id
                      JOIN bd_gc a ON b.gc_id = a.gc_id
                      WHERE d.[gw_isdelete] = 1 AND d.scx_id = @scx_id ";

				// 动态构建 WHERE 子句
				List<string> conditions = new List<string>();
				if (!string.IsNullOrEmpty(str))
				{
					conditions.Add("(d.[gw_code] LIKE @str OR d.[gw_name] LIKE @str)");
				}
				if (date1 != null && date2 != null)
				{
					conditions.Add("d.gw_create_time BETWEEN @date1 AND @date2");
				}

				else if (date1 != null)
				{
					conditions.Add("d.gw_create_time >= @date1");
				}

				else if (date2 != null)
				{
					conditions.Add("d.gw_create_time <= @date2");
				}

				// 拼接条件
				if (conditions.Count > 0)
				{
					sql += " AND " + string.Join(" AND ", conditions);
				}

				connection.Open();
				using (SqlCommand command = new SqlCommand(sql, connection))
				{
					// 参数化查询
					command.Parameters.AddWithValue("@scx_id", scx_id);
					if (!string.IsNullOrEmpty(str))
					{
						command.Parameters.AddWithValue("@str", $"%{str}%");
					}
					if (date1 != null)
					{
						command.Parameters.AddWithValue("@date1", date1.Value.Date);
					}
					if (date2 != null)
					{
						command.Parameters.AddWithValue("@date2", date2.Value.Date.AddDays(1).AddSeconds(-1)); // 包含当天
					}

					using (SqlDataReader reader = command.ExecuteReader())
					{
						while (reader.Read())
						{
							gw.Add(new GW
							{
								gw_id = Convert.ToInt32(reader["gw_id"]),
								gw_create_time = Convert.ToDateTime(reader["gw_create_time"]),
								gw_code = reader["gw_code"].ToString(),
								gw_name = reader["gw_name"].ToString(),
								gc_name = reader["gc_name"].ToString(),
								cj_name = reader["cj_name"].ToString(),
								scx_name = reader["scx_name"].ToString(),
								scx_id = Convert.ToInt32(reader["scx_id"]),
								gw_remark = reader["gw_remark"].ToString(),
								gw_create_by = reader["gw_create_by"] is DBNull ? 0 : Convert.ToInt32(reader["gw_create_by"]),
							});
						}
					}
				}
			}
			return gw;
		}

		/// <summary>
		/// 新增工位
		/// </summary>
		/// <param name="gw"></param>
		/// <returns></returns>
		public bool AddGW(GW gw)
		{
			string sql = @"insert into [dbo].[bd_gw]
(scx_id, gw_code, gw_name, gw_remark, gw_create_by)
values
(@scx_id,@code,@name,@remark,1)";

			try
			{
				using (SqlConnection connection = new SqlConnection(data))
				{
					connection.Open();
					using (SqlCommand command = new SqlCommand(sql, connection))
					{
						command.Parameters.AddWithValue("@scx_id", gw.scx_id);
						command.Parameters.AddWithValue("@code", gw.gw_code);
						command.Parameters.AddWithValue("@name", gw.gw_name);
						command.Parameters.AddWithValue("@remark", gw.gw_remark);

						int rowsAffected = command.ExecuteNonQuery();
						return rowsAffected > 0; // 如果插入成功，返回 true
					}
				}
			}
			catch (Exception ex)
			{
				// 记录日志或处理异常
				Console.WriteLine("An error occurred: " + ex.Message);
				return false; // 如果发生异常，返回 false
			}
		}

		/// <summary>
		/// 修改工位
		/// </summary>
		/// <param name="gw"></param>
		/// <returns></returns>
		public bool UpdateGW(GW gw)
		{
			string sql = @"update [dbo].[bd_gw] set 
 scx_id=@scx_id, gw_name=@name, gw_remark=@remark,
[gw_update_by]=1,[gw_update_time]=getdate()
where [gw_id]=@id";

			try
			{
				using (SqlConnection connection = new SqlConnection(data))
				{
					connection.Open();
					using (SqlCommand command = new SqlCommand(sql, connection))
					{
						command.Parameters.AddWithValue("@scx_id", gw.scx_id);
						command.Parameters.AddWithValue("@name", gw.gw_name);
						command.Parameters.AddWithValue("@remark", gw.gw_remark);
						command.Parameters.AddWithValue("@id", gw.gw_id);

						int rowsAffected = command.ExecuteNonQuery();
						return rowsAffected > 0; // 如果插入成功，返回 true
					}
				}
			}
			catch (Exception ex)
			{
				// 记录日志或处理异常
				Console.WriteLine("An error occurred: " + ex.Message);
				return false; // 如果发生异常，返回 false
			}
		}

		/// <summary>
		/// 删除工位
		/// </summary>
		/// <param name="id"></param>
		/// <returns></returns>
		public bool DelGW(int id)
		{
			try
			{
				using (SqlConnection connection = new SqlConnection(data))
				{
					string sql = @"UPDATE [dbo].[bd_gw] 
                       SET gw_isdelete = 0 
                       WHERE gw_id = @Id";

					return connection.Execute(sql, new { Id = id }) > 0;
				}
			}
			catch (Exception ex)
			{
				// 记录日志等异常处理
				Console.WriteLine($"删除失败: {ex.Message}");
				return false;
			}
		}

		/// <summary>
		/// 批量删除工位
		/// </summary>
		/// <param name="ids"></param>
		/// <returns></returns>
		public bool DelGWs(int[] ids)
		{
			// 参数验证
			if (ids == null || ids.Length == 0)
				return false;

			try
			{
				using (SqlConnection connection = new SqlConnection(data))
				{
					connection.Open();
					using (var transaction = connection.BeginTransaction()) // 添加事务
					{
						try
						{
							string sql = @"UPDATE [dbo].[bd_gw] 
		                                 SET gw_isdelete = 0 
		                                 WHERE gw_id IN @Ids";

							int affectedRows = connection.Execute(sql, new { Ids = ids }, transaction);
							transaction.Commit(); // 提交事务

							// 确保所有ID都成功更新
							return affectedRows == ids.Length;
						}
						catch
						{
							transaction.Rollback(); // 回滚事务
							throw;
						}
					}
				}
			}
			catch (Exception ex)
			{
				// 记录日志等异常处理
				Console.WriteLine($"批量删除失败: {ex.Message}");
				return false;
			}
		}

		#endregion


	}
}
